# coding:utf-8
import os
import time
from common.writeExcel import *
import paramiko
import openpyxl
from common.sqlOperate import *

# SSH连接参数
hostname = '192.168.3.91'
port = 22
username = 'pi'
password = 'gwza2017999'
# # 远程脚本路径
script_path = 'wclog'
# 建立SSH连接
# ssh = paramiko.SSHClient()
# ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# ssh.connect(hostname, port, username, password)
# print('连接网关成功')
filename = 'gate_result.xlsx'
# we = writeExcel(filename,1)
# ws = we.create_excel_sheet()
# filename='result.xlsx'
we = writeExcel(filename)
ws = we.create_excel_sheet()
device_dict1 = {}  # 定义一个字典
device_dict2 = {}

for i in range(2):
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(hostname, port, username, password)
    print('连接网关成功')
    list_device = []
    # 执行远程命令
    stdin, stdout, stderr = ssh.exec_command(f"bash {script_path}")
    print('已执行命令，等待结果中')
    stdin.close()
    # 获取命令输出和错误信息
    output = stdout.read().decode('utf-8')
    error = stderr.read().decode('utf-8')
    # 将输出结果按行分割
    # lines = output.strip().split('\n')
    lines = output.split('\n')
    # print(len(lines))
    ssh.close()
    print('网关已关闭')

    for line in lines:
        list_simple = []
        list_simple.append(line)
        list_device.append(list_simple)
        fre_point, device_id, device_num, t_used = '', '', '', ''
        if ': GW' in line:  # 将数据筛选
            print(line)
            device_things = line.split(':')
            fre_point = device_things[0]  # 获取频点
            device_id = device_things[1].strip()  # 获取id
            device_num = int(device_things[2])  # 获取网关收到的数据
        elif 'tused' in line:  # 获取当前的运行秒数
            t_used = line.split('=')
            t_used = int(t_used[1])

        if i == 0: #将两次结果分开记录
            if device_id != '':
                device_dict1['fre_point'] = fre_point
                device_dict1[device_id] = device_num
            elif t_used != '':
                device_dict1['time'] = t_used
        elif i == 1:
            if device_id != '':
                device_dict1['fre_point'] = fre_point
                device_dict2[device_id] = device_num
            elif t_used != '':
                device_dict2['time'] = t_used

    we.append_to_excel2(list_device, ws)



    # 如果有错误，打印错误信息
    if error:
        print("Error:", error)

    if i != 1:
        j =20
        while j>0:
            print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
            time.sleep(60)
            j =j-1



host = '192.168.3.44'
#host = 'localhost'
port = 3306
#user = 'root'
#password = 'root1234'
user = 'test123'
password = 'test1234'
#db='gwza0204'
db = 'gwza0306'
#db = 'uwb44'
#db = 'gwza_refactory_local'
name = "4_10复复第二次60"
#id = 688
ob = sqlOperate(host, port, user, password, db)
ob.sqlConnect()
id = ob.reqRuleId(name)
num,devices = ob.reqHelmetVlt(id)

arr2 = []
t1 = device_dict1['time']
t2 = device_dict2['time']
t = t2-t1
print(t)
for deviceid in devices:
    arr1=[]
    id = deviceid[0]
    print(id)
    per=0
    fre_point1=''
    if all(id in d for d in [device_dict1, device_dict2]):
        fre_point1 = device_dict1['fre_point']
        n1 = device_dict1[id]
        n2 = device_dict2[id]
        n = n2-n1
        result = (n/t)*100
        per = round(result, 1)
        print(per)
    arr1.append(fre_point1)
    arr1.append(id)
    arr1.append(per)
    arr2.append(arr1)
we.append_to_excel2(arr2,ws)
we.save_to_excel()

ssh.close()

